PostgreSQL主备切换
备库如何激活
在PostgreSQL(HOT-Standby)如主库出现异常。备库如何激活；来替换主库工作。有下列2种方式
- 备库在recovery.conf文件中有个配置项trigger_file。它是激活standby的触发文件。当它存在；就会激活standby。
- 使用pg_ctl promote来激活。
演示场景
模拟在主库异常挂掉。备库来替换主库工作一段时间。并原主库切换成新备库并恢复操作。 环境 主机名 IP地址 角色 数据目录 postgres202 192.168.1.202 primary /home/postgres/data postgres203 192.168.1.203 standby /home/postgres/data postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 7687 usesysid | 24576 usename | replica application_name | walreceiver client_addr | 192.168.1.203 client_hostname | client_port | 60234 backend_start | 2017-09-04 22:08:26.249452+08 backend_xmin | 1680 state | streaming sent_location | 0/B0000D0 write_location | 0/B0000D0 flush_location | 0/B0000D0 replay_location | 0/B0000D0 sync_priority | 0 sync_state | async 2.1 模拟主库关机 [postgres@postgres202 ~]$ pg_stop waiting for server to shut down........ done server stopped 2.2 激活备库； 模拟新主库工作一段时间。操作删除T2表和新建T3表 [postgres@postgres203 data]$ pg_ctl promote server promoting [postgres@postgres203 ~]$ psql lottu lottu psql (9.6.0) Type "help" for help. lottu=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+------- lottu | t | table | lottu lottu | t2 | table | lottu (2 rows) lottu=> drop table t2; DROP TABLE lottu=> create table t3 as select * from t; SELECT 12 #切换日志；进行归档。 [postgres@postgres203 ~]$ psql psql (9.6.0) Type "help" for help. postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/C018FC0 (1 row) postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/D000078 (1 row) postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/E000000 (1 row) 2.3 恢复主库 1. 用pg_rewind命令同步新备库。 [postgres@postgres203 ~]$ pg_rewind -? pg_rewind resynchronizes a PostgreSQL cluster with another copy of the cluster. Usage: pg_rewind [OPTION]... Options: -D, --target-pgdata=DIRECTORY existing data directory to modify --source-pgdata=DIRECTORY source data directory to synchronize with --source-server=CONNSTR source server to synchronize with -n, --dry-run stop before modifying anything -P, --progress write progress messages --debug write a lot of debug messages -V, --version output version information, then exit -?, --help show this help, then exit Report bugs to <pgsql-bugs@postgresql.org>. [postgres@postgres202 ~]$ pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.1.203 port=5432 user=postgres dbname=postgres' -P connected to server servers diverged at WAL position 0/C000098 on timeline 1 rewinding from last common checkpoint at 0/C000028 on timeline 1 reading source file list reading target file list reading WAL in target need to copy 203 MB (total source directory size is 221 MB) 208264/208264 kB (100%) copied creating backup label and updating control file syncing target data directory Done! [postgres@postgres202 data]$ mv recovery.done recovery.conf [postgres@postgres202 data]$ vi recovery.conf standby_mode = on # 这个说明这台机器为从库 primary_conninfo = 'host=192.168.1.203 port=5432 user=replica password=replica' # 这个说明这台机器对应主库的信息 recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据 由于postgresql.conf是同步过来的；所有不做修改。也有几个地方要进行修改 max_connections = 200 # 一般查多于写的应用从库的最大连接数要比较大 hot_standby = on # 说明这台机器不仅仅是用于数据归档，也用于数据查询 max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间 wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间 hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈 【验证】 [postgres@postgres202 data]$ pg_start server starting [postgres@postgres202 data]$ psql lottu lottu psql (9.6.0) Type "help" for help. lottu=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+------- lottu | t | table | lottu lottu | t3 | table | lottu (2 rows) [postgres@postgres203 data]$ psql psql (9.6.0) Type "help" for help. postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 4193 usesysid | 24576 usename | replica application_name | walreceiver client_addr | 192.168.1.202 client_hostname | client_port | 60078 backend_start | 2017-09-02 07:56:08.390662+08 backend_xmin | 1688 state | streaming sent_location | 0/F01EEA8 write_location | 0/F01EEA8 flush_location | 0/F01EEA8 replay_location | 0/F01EEA8 sync_priority | 0 sync_state | async
参考文献：http://www.cnblogs.com/songyuejie/p/4976171.html
